import pymysql.cursors
import plotly.plotly as py
from plotly.graph_objs import *

def mark_points(points):
    data = Data([
        Scattermapbox(
            lat=points["lat"],
            lon=points["lon"],
            mode='markers',
            marker=Marker(
                size=9
            ),
        )
    ])
    layout = Layout(
        autosize=True,
        hovermode='closest',
        mapbox=dict(
            bearing=0,
            pitch=0,
            zoom=10
        ),
    )

    fig = dict(data=data, layout=layout)
    py.plot(fig, filename='Multiple Mapbox')

def read_port_position():
    lon = []
    lat = []
    connection = pymysql.connect(host='localhost',
                                 user='root',
                                 password='123456',
                                 db='aisdb_suntao',
                                 charset='utf8mb4',
                                 cursorclass=pymysql.cursors.DictCursor)

    try:
        with connection.cursor() as cursor:
            cursor.execute("select mmsi from train_mmsi where ship_type = '1' limit 1000")
            shiplist = cursor.fetchall()
            mmsilist = [mmsi["mmsi"] for mmsi in shiplist]

            for mmsi in mmsilist:
                cursor.execute("SELECT distinct(World_port_index_number_src)"
                               "FROM aisdb_suntao.l2_port_port_journey_15 where MMSI = %s", mmsi)
                portlist = cursor.fetchall()

                portset = set()
                for port in portlist:
                    portset.add(port["World_port_index_number_src"])

            for portid in portset:
                cursor.execute("SELECT Longitude, Latitude FROM aisdb_suntao.my_wpi_ports where World_port_index_number = %s", portid)
                portpositon = cursor.fetchall()
                for position in portpositon:
                    lon.append(str(int(position["Longitude"]) / 600000.0))
                    lat.append(str(int(position["Latitude"]) / 600000.0))


    finally:
        connection.close()

    points = dict(lon=lon, lat=lat)

    mark_points(points)

if __name__ == "__main__":
    read_port_position()